﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using KPIS.GERP.Models;
using KPIS.GERP.Models.FIM;
using OfficeOpenXml;

namespace KPIS.GERP.WEB.GIMS.Controllers
{
    public class ModelTest
    {
        public bool Col1 { get; set; }
        public bool? Col2 { get; set; }
    }
    public class _TestController : Controller
    {
        public ActionResult ReCutStock()
        {
            return View();
        }

        public ActionResult ImportInitialParcel()
        {
            return View();
        }
        public ActionResult RemoveDuplication()
        {
            return View();
        }
        public ActionResult Index()
        {
            var model = new ModelTest();
            return View(model);
        }

        public ActionResult _UserRole()
        { 
            return View();
        }
        
        [HttpPost]
        public ActionResult Index(ModelTest model)
        {
            
            return View(model);
        }

        public ActionResult UpdateAbsenceNullCode()
        {
            return View();
        }

        public class ImportMasContactBankVM
        {
            public int ExcelRow { get; set; }
            public string fullname { get; set; }
            public string account { get; set; }
            public int bank_Code { get; set; }
            public string bank_Name { get; set; }
            public int brance_code { get; set; }
        }

        public ActionResult ImportMasContactBank()
        {
            int lastRowNum;
            var debug = new List<string>();
            var excelData = new List<ImportMasContactBankVM>();

            using (ExcelPackage pck = new ExcelPackage(new FileInfo(@"D:\MyProjects\Project - CAT\beneficiary_250457 - Copy.xlsx"), true))
            {
                var ws1 = pck.Workbook.Worksheets["beneficiary"];

                lastRowNum = ws1.Dimension.End.Row;

                //Normalizing and collect data to model array
                for (int i = 2; i <= lastRowNum; i++)
                {
                    var im = new ImportMasContactBankVM();
                    im.ExcelRow = i;
                    im.fullname = Convert.ToString(ws1.Cells["C" + i].Value);
                    im.account = Convert.ToString(ws1.Cells["D" + i].Value);
                    im.bank_Code = Convert.ToInt32(ws1.Cells["N" + i].Value);
                    im.bank_Name = Convert.ToString(ws1.Cells["O" + i].Value);
                    im.brance_code = Convert.ToInt32(ws1.Cells["P" + i].Value);
                    excelData.Add(im);
                }
            }

            var db = new GERPContext(true);
            // Validate bankcode, bankname, branch_code
            foreach(var im in excelData)
            {
                var bank = db.MasBanks.Where(o => o.RecordStatus != "D").ToList().FirstOrDefault(o=>
                                                  Convert.ToInt32(o.Code) == im.bank_Code);
                var branch = db.MasBankBranchs.Where(o => o.RecordStatus != "D").ToList().FirstOrDefault(o=>
                                                          o.BankId == bank.BankId &&
                                                          Convert.ToInt32(o.Code) == im.brance_code);
                if(bank == null )
                {
                    throw new NullReferenceException("Not found bank or branch");
                }else if(branch == null)
                {
                    db.MasBankBranchs.Add(new MasBankBranch
                        {
                            Code = im.brance_code.ToString(), BranchName = "???", BankId = bank.BankId, RecordStatus = "U", CreatedBy = 1, CreatedWhen = DateTime.Now, UpdatedBy = 1, UpdatedWhen = DateTime.Now
                        });
                }


            }
            return null;
        }

        public class ImportMasBankBranchVM
        {
            public int ExcelRow { get; set; } 
            public int branch_Code { get; set; }
            public string branch_Name { get; set; } 
        }

        public ActionResult ImportMasBankBranch()
        {
            int lastRowNum;
            var debug = new List<string>();
            var excelData = new List<ImportMasBankBranchVM>();

            using (ExcelPackage pck = new ExcelPackage(new FileInfo(@"D:\MyProjects\Project - CAT\ScbBranch - Copy.xlsx"), true))
            {
                var ws1 = pck.Workbook.Worksheets["Sheet1"];

                lastRowNum = ws1.Dimension.End.Row;

                //Normalizing and collect data to model array
                for (int i = 2; i <= lastRowNum; i++)
                {
                    var im = new ImportMasBankBranchVM();
                    im.ExcelRow = i;
                    im.branch_Code = Convert.ToInt32(ws1.Cells["A" + i].Value);
                    im.branch_Name = Convert.ToString(ws1.Cells["B" + i].Value); 
                    excelData.Add(im);
                }
            }

            var db = new GERPContext(true);
            // Validate bankcode, bankname, branch_code
            int countAlreadyExist = 0, countNotExist = 0, countAlreadyExistAndMatchName = 0,countAlreadyExistAndNotMatchName = 0;
            var notMatchName = new List<string>();
            var matchName = new List<string>();
            foreach(var im in excelData)
            { 
                var branch = db.MasBankBranchs.Where(o => o.RecordStatus != "D").ToList().FirstOrDefault(o=>
                                                          o.BankId == 14 &&
                                                          Convert.ToInt32(o.Code) == im.branch_Code); 
                if(branch != null)
                {
                    countAlreadyExist++;
                    if(branch.BranchName == im.branch_Name)
                    {
                        countAlreadyExistAndMatchName++;
                        matchName.Add("Im[" + im.branch_Code+ ":" + im.branch_Name + ", " + branch.BranchName + "]");
                    } else
                    {
                        countAlreadyExistAndNotMatchName ++;
                        notMatchName.Add("Im[" + im.branch_Code+ ":" + im.branch_Name + ", " + branch.BranchName + "]");
                    }
                } else
                {
                    countNotExist++;
                    db.MasBankBranchs.Add(new MasBankBranch
                        {
                            Code = Convert.ToString(im.branch_Code), BranchName = im.branch_Name, BankId = 14, RecordStatus = "U", CreatedBy = 1, CreatedWhen = DateTime.Now, UpdatedBy = 1, UpdatedWhen = DateTime.Now
                        });
                }
            }
            db.SaveChanges();
            string matchNameStr = string.Join("\r\n", matchName);
            string notMatchNameStr = string.Join("\r\n", notMatchName);
            return null;
        }
    }
}
